File
SQL
Description
Simple_Select.sql select * from employee This sample illustrates use of simple data retrieval from single table.
Distinct.sql select distinct FirstName from employee This sample illustrates use of DISTINCT keyword. The query returns all names from the field "FirstName" excluding duplicates.
Select_Top.sql select Top 5,4 * from venues This sample illustrates use of TOP option. The query retrieves first five entries beginning with 4th.
Select_Order_By.sql

select Event_Name, Venue, Ticket_Price
from Events e, Venues v
where e.VenueNo = v.VenueNo
order by Ticket_Price asc, Venue desc

This example illustrates use of ORDER BY clause. The query lists all events and venues where they take place. Result dataset is sorted by ticket price (ascending) and venue name (descending order).
Select_Union.sql select Event_Name from events where VenueNo<3
union
select Event_Name from events where VenueNo>8
This sample illustrates use of UNION keyword. In this query results of two "select" statements are combined in single dataset.

Select_Intersect.sql

select VenueNo from Venues
intersect corresponding by (VenueNo)
select VenueNo fromEvents
This sample illustrates use of INTERSECT keyword. The query shows only those VenueNo that are present in both tables.
Select_Having.sql select VenueNo, Avg(Ticket_Price) from events
group by VenueNo
Having Event_Date<now
This sample illustrates use of HAVING clause. The query retrieves average ticket price for every venue using events that happened before.
Select_Group_By.sql select FirstName, count(FirstName) cnt
from Employee
group by FirstName
order by cnt desc
This example illustrates use of GROUP BY keywords. After performing this query the resulting dataset will contain names of employees and a number - how many times this name is met in the table.
Except_Corresponding.sql select * from Venues
except corresponding by (VenueNo)
select * from Events
This query illustrates use of EXCEPT keyword. In the example below dataset returned will contain all entries from field "VenueNo" that exist in table "Venues" but do NOT exist in table "Events".
Expr_Arithmetic.sql select (ticket_price+10)*2 as price from events This sample illustrates use of arithmetic functions in queries. In this query for every entry value of field "ticket_price" is added to 10 and then multiplied by two. The result column is given name "price".
Select_Different_DB.sql select Event_Name, Venue, Ticket_Price
from "C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Events e, "C:\Program Files\AbsoluteDatabase\Demos\Data\Demos.abs".Venues v
where e.VenueNo = v.VenueNo
This example illustrates get data from TWO DATABASES IN SAME QUERY. The query lists all events, event prices and venues where they take place. Note that you can specify two different databases.
Select_From_2_Tables.sql select Event_Name, Venue
from Events e, Venues v
where e.VenueNo = v.VenueNo
order by Event_Name, Venue
This example illustrates how to get data from two tables using EQUI-JOIN. The query lists all events and venues where they take place. Notice that only those entries will be shown that have corresponding values in both tables. If event name has empty field "VenueNo" or its value is never met in same field of table "Venues", then this event will not be included in the resulting dataset.
Inner_Join.sql select Event_Name, Venue
from Events e
inner join Venues v ON (e.VenueNo=v.VenueNo)
order by Event_Name
This sample illustrates use of INNER JOIN statment. The query shows events and venues where it will occur. Notice that if VenueNo corresponding to event is not found in "venues" table, it will not be shown.
Left_Join.sql select Event_Name, Venue
from Events e
left join Venues v on e.VenueNo = v.VenueNo
order by Event_Name, Venue
This example illustrates use of LEFT JOIN action. This query retrieves all event names and places where they happen. Venue is attached to event name if value of field "VenueNo" is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it.
Full_Join.sql select Event_Name, Venue
from Events e
full join Venues v on e.VenueNo = v.VenueNo
order by Event_Name, Venue
This example illustrates use of FULL JOIN action. This query retrieves all event names and places where they happen. Venue is attached to event name if value of field "VenueNo" is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it. Likewise, if a corresponding event for a venue couldn't be found, NULL is placed to the left of it.
Natural_Full_Join.sql select Event_Name, Venue
from Events
natural full join Venues
order by Event_Name, Venue
This example illustrates use of FULL JOIN action with NATURAL option. This query retrieves all event names and places where they happen. Venue is attached to event name if value of same named field is equal in both tables. If there is no corresponding venue for event, then NULL is placed beside it. Likewise, if a corresponding event for a venue couldn't be found, NULL is placed to the left of it.
Function_Avg.sql select VenueNo, Avg(Ticket_Price) from events group by VenueNo This sample illustrates use of AVG function. The query retrieves average ticket price for every venue.
Function_Cast.sql select Event_Name, Cast(Event_Date As String) from events This sample illustrates use of CAST function. The query retrieves all events and their dates as strings.
Function_Count.sql select count(*) c from Employee This sample illustrates use of COUNT function. This query counts number of rows in the table "Employee". Having performed it, you will have one column named "c" and one row in it containing number of rows in the table.
Function_Length.sql select Event_Name, Length(Event_Name) from events This sample illustrates use of LENGTH function. The query retrieves events' names and their lengths.
Function_Lower.sql select Event_Name, Lower(Event_Name) from events This sample illustrates use of LOWER function. The query retrieves events' names in normal capitalization and with all letters in lower case.
Function_Max.sql select max(Capacity) from venues This sample illustrates use of MAX function. The query retrieves maximal capacity from venues.
Function_Min.sql select min(Capacity) from venues This sample illustrates use of MIN function. The query retrieves minimal capacity from venues.
Function_Pos.sql select Event_Name, Pos('Finals', Event_Name) from events This sample illustrates use of POS function. The query retrieves events' names address of position of the word 'Finals' in it.
Function_Substring.sql select SubString(Event_Name,2,9) from events This sample illustrates use of SUBSTRING function. The query retrieves events' names where first letter is excluded as well as letters after tenth.
Function_Sum.sql select VenueNo, Sum(Ticket_Price) from events group by VenueNo This sample illustrates use of SUM function. The query retrieves sum of ticket prices for every venue.
Function_Sysdate_Now.sql select Event_Name, Event_Date, sysdate as CurDate
from events
where Event_Date < now
This sample illustrates use of SYSDATE and NOW functions. The query retrieves all events that have occured already. Notice that functions NOW and SYSDATE do same things and you can use either.
Function_ToDate.sql select Event_Name, Event_Date, sysdate as CurDate from events
where Event_Date < ToDate('11/19/2003 10:15:30 am','MM/DD/YYYY hh:nn:ss ampm')
This sample illustrates use of TODATE function. The query retrieves all events that have occured before the specified date.
Function_ToString.sql select Event_Name, ToString(Event_Date,
"'Event date is 'mm/dd/yyyy hh24:nn:ss '!'") Formated_Date from events
where Event_Date < now
This sample illustrates use of TOSTRING function. The query retrieves all events that have occured already together with formatted date.
Function_Trim.sql select Trim(' '+Event_Name+' ') from events This sample illustrates use of TRIM function. The query retrieves events' names. Although some blanks are appended on both sides of column "Event_Name", function trim removes them. Use LTRIM or RTRIM if you want to remove blanks from on side only.
Str_Concat.sql select FirstName + ' ' + LastName as Name from employee This sample illustrates how to use string concatenation. The query constructs a column "Name" using field "FirstName", then blank space, then field "LastName" from table "Employee".
Str_Concat_2.sql select FirstName || ' ' || LastName as Name from employee This sample illustrates how to use string concatenation. The query constructs a column "Name" using field "FirstName", then blank space, then field "LastName" from table "Employee".
Simple_Case.sql select event_name, case VenueNo
when 2 then 'Memorial Stadium'
when 4 then 'Coliseum'
else 'unknown venue: VenueNo='+VenueNo
end as Venue
from events
This sample illustrates use of simple CASE function. For each row in the dataset being retrieved, field "VenueNo" is compared to 2, and, if it is true, the string 'Memorial Stadium' appears to the right of event name. If it is not true, then "VenueNo" is compared to 4, and if it equals to 4, string 'Coliseum' appears. If "EventNo" matches neither 2 nor 4, ELSE part of the statement is executed and 'unknown venue: VenueNo='is shown followed by value of the field "VenueNo".
Searched_Case.sql select Event_Name, Case
when Event_Date when Event_Date>now then 'In the future'
else 'Right now' end as 'When' from events
This sample illustrates use of SEARCHED CASE construction. The query retrieves all events and notification whether it had happened already.
Operator_Like.sql select * from events where Event_Name Like '%men%' This sample illustrates use of LIKE operator. The query retrieves all records from "events" where "Event_Name" contains substring 'men'.
Operator_Between.sql select * from events where VenueNo Between 3 and 8 This sample illustrates use of BETWEEN operator. The query retrieves all records from "events" where VenueNo is more than 2 and less than 9.
In_Operator.sql select * from Employee where FirstName in ('Roberto', 'Roger')
order by FirstName
This example shows how to use keyword IN. The query retrieves all data from table "Employee" concerning employees with names 'Roberto' or 'Roger'.
Function_Upper.sql select Event_Name, Upper(Event_Name) from events This sample illustrates use of UPPER function. The query retrieves events' names in normal capitalization and with all letters in upper case.
Subquery_where_exists.sql select Venue from venues v where not exists (select * from events
where VenueNo=v.VenueNo)
This sample demonstrates use of subquery in where clause together with EXISTS keyword. The query returns all venues where no event will take place.
Subquery_where_in.sql select Venue from venues where VenueNo in (select VenueNo from events) This sample illustrates use of subquery in where clause together with IN keyword. The query returns only those venues where some event will occur.
Subquery_where_equal.sql select Venue from venues v where 1 = (select count(*) from events where VenueNo=v.VenueNo) This sample illustrates use of subquery in where clause when result being evaluated is compared to a constant. This query shows all venues where only one event will occur.
Subquery_where_compare_any.sql select e.Event_Name, v.Capacity from events e, venues v
where e.VenueNo=v.VenueNo and v.Capacity > any (select Capacity from venues where VenueNo between 3 and 4)
This example illustrates use of keyword ANY in where clause. This query returns events that will occur on venues with capacity more than in venue 3 or 4.
Subquery_where_compare_all.sql select e.Event_Name, v.Capacity from events e, venues v
where e.VenueNo=v.VenueNo and v.Capacity < all (select Capacity from venues where VenueNo between 3 and 4)
This example illustrates use of keyword ALL in where clause. This query returns events that will occur on venues with capacity lower than in venues 3 and 4.
subquery_update.sql update Events e
set Ticket_price = (select Capacity from Venues where VenueNo=e.VenueNo)*2
where Ticket_price <= 5
This sample demonstrates use of subquery in SET clause. The query works as follows. First, only those entries are picked that have "Ticket_price" 5 of less. Then corresponding venue is taken, its capacity is multiplied by two and set as the new ticket price. Notice that NULL fields are not affected.
Subquery_select_as_field.sql select Venue, (select count(*) from Events where VenueNo = v.VenueNo) EventsCount
from Venues v
order by EventsCount desc
This sample demonstrates use of subquery representing a separate field. For each entry in table "Venues" the query counts quantity of events from table "Events" and places this number in a column named "EventsCount".
Subquery_select_from_select.sql select count(*) UniqueNamesCount from (select distinct FirstName
from Employee)
This sample illustrates use of a subquery in FROM clause. The subquery retrieves count of unique names in table "Employee", then function "count" counts quantity of these names.
Subquery_Insert_Into.sql drop table venues2;
create table venues2
( VenueNo autoinc, Venue varchar(30), Capacity integer, Venue_Map graphic, Remarks memo, primary key idxPrimaryKey (VenueNo) );
insert into Venues2
select * from venues v
where not exists (select * from events where VenueNo=v.VenueNo)
This sample demonstrates use of subquery in INSERT INTO clause. The first query in the script deletes table Venues2. Next one creates it. The third query inserts into the new table all venues where no event will take place. Notice that two subqueries here are enclosed one into another.
Alter_Table_Add.sql drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
alter table MyTable add (val3 float);
select * from MyTable order by ID val2 desc;
This sample illustrates use of ALTER TABLE statement with ADD option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table with 3 fields. Queries 3 - 6 insert some data into the table.
The seventh query adds a new column named "val3" of type float to the table. Query #8 retrieves all rows from this table.
Alter_Table_Memory.sql drop table memory MyTable;
create table memory MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into memory MyTable (val1, val2) values (2,'2');
insert into memory MyTable (val1, val2) values (4,'3');
insert into memory MyTable (val1, val2) values (5,'9');
insert into memory MyTable (val1, val2) values (7,'Hello');
alter table memory MyTable add (val3 float);
select * from memory MyTable order by ID val2 desc
This sample illustrates use of ALTER TABLE statement with MEMORY option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table IN-MEMORY with 3 fields. Queries 3 - 6 insert some data into the table. The seventh query adds a new column named "val3" of type float to the table. Query #8 retrieves all rows from this table.
Alter_Table_Modify.sql drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
alter table MyTable mofify (val1 string(5));
select * from MyTable order by ID val2 desc;
This sample illustrates use of ALTER TABLE statement with MODIFY option.
This SQL script contains eight queries. First one deletes table named "MyTable". The second creates this table with 2 fields. Queries 3 - 6 insert some data into the table.
The seventh query modifies column named "val1" so that it turns to be string. Query #8 retrieves all rows from this table.
Create_Drop_Table.sql drop table developers;
create table developers
( id AutoInc, Code Integer, Name varchar(20), Birthday Date, Photo Graphic
BlobCompressionMode 9
BlobBlockSize 1024
BlobCompressionAlgorithm ZLib );
select * from developers;
This sample demonstrates use of CREATE TABLE and DROP TABLE statements. This SQL script contains three queries. First one deletes table named "developers". The second creates this table with 5 fields and 3 database options. The third query retrieves all rows from this table (of course, the table is empty at this point).
Create_Index.sql drop table developers;
create table developers ( id AutoInc, Code Integer, Name varchar(20) );
insert into developers (code, name) values (2, 'Janet');
create unique index UniqueName ON developers (name desc nocase, code); select * from developers;
This sample illustrates use of CREATE INDEX statement. This SQL script contains five queries. First one deletes table named "developers". The second creates this table with 3 fields. The third query inserts some data into the table. The fourth creates index so that "name"+"code" combination must be unique, for field "name" sorting order is descending and is case-insensitive. The new index is given name "UniqueName". The fifth query retrieves all rows from this table.
Create_InMemory_Table.sql drop table memory developers;
create table memory developers (id AutoInc, code Integer, name varchar(20));
insert into memory developers (code, name) values (5, 'Bob');
insert into memory developers (code, name) values (7, 'Jeck');
insert into memory developers (code, name) values (2, 'Carol');
insert into memory developers (code, name) values (5, 'Harry');
insert into memory developers (code, name) values (4, 'Sam');
insert into memory developers (code, name) values (9, 'Chris');
select * from memory developers order by name desc, code, id;
This sample illustrates use of CREATE TABLE statement with MEMORY option. This SQL script contains nine queries. First one deletes table named "developers". The second creates this table in-memory with 3 fields. Queries 3-8 insert some data into the table. The ninth query retrieves all rows from this table.
Delete.sql drop table developers;
create table developers (id AutoInc, code Integer, name varchar(20));
insert into developers (code, name) values (5, 'Bob');
insert into developers (code, name) values (7, 'Jeck');
delete from developers where name='Bob';
select * from developers order by name desc, code, id
This sample illustrates use of DELETE keyword. This SQL script contains six queries. First one deletes table named "developers". The second creates this table with 3 fields. Queries 3 and 4 insert some data into the table. The next query deletes all strings in the table that have 'Bob' as value of "name" field. The sixth query retrieves all rows from this table.
Drop_Index.sql drop table developers;
create table developers ( id AutoInc, Code Integer, Name varchar(20) );
insert into developers (code, name) values (2, 'Janet');
create unique index UniqueName ON developers (name desc nocase, code); drop index developers.UniqueName;
select * from developers;
This sample illustrates use of DROP INDEX statement. This SQL script contains six queries. First one deletes table named "developers". The second creates this table with 3 fields. Query #3 inserts some data into the table. The fourth creates index so that "name"+"code" combination must be unique, for field "name" sorting order is descending and is case-insensitive. The new index is given name "UniqueName". The fifth query deletes index "UniqueName" from table "developers". The sixth query retrieves all rows from this table.
Insert_Into.sql drop table developers;
create table developers (id AutoInc, code Integer, name varchar(20));
insert into developers (code, name) values (5, 'Bob');
insert into developers (code, name) values (7, 'Jeck');
insert into developers (code, name) values (2, 'Carol');
insert into developers values (10, 5, 'Harry');
insert into developers (code, name) values (4, 'Sam');
insert into developers (code, name) values (9, 'Chris');
select * from developers order by name desc, code, id;
This sample illustrates use of INSERT statement. This SQL script contains nine queries. First one deletes table named "developers". The second creates this table with 3 fields. Queries 3-8 insert some data into the table. The ninth query retrieves all rows from this table. Notice that you don't have to specify column names as long as you let parameters follow the order of columns in the table (line 6).
Select_Into.sql select distinct FirstName into NewTable from employee This sample illustrates use of SELECT INTO statement. The query returns all names from the field "FirstName" excluding duplicates and places it inside a new table giving it name "NewTable".
Transactions_Flush.sql drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
start transaction;
insert into MyTable (val1, val2) values (5,'Flush1');
insert into MyTable (val1, val2) values (7,'Flush2');
commit flush;
select * from MyTable;

This sample illustrates use of FLUSH option in COMMIT statement.
This SQL script contains 9 queries. Table "test" is deleted, then recreated and filled with data. A transaction is started, data is inserted, then changes are comitted and immediately flushed to disk. At last you can see what data is contained in the table after all queries.

Transactions_SQL.sql drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'Start');
start transaction;
insert into MyTable (val1, val2) values (4,'Rollback');
rollback;
start transaction;
insert into MyTable (val1, val2) values (5,'Commit');
commit;
select * from MyTable;
This sample illustrates use of TRANSACTIONS via SQL. Notice that TAbsDatabase supports transactions as class methods so you might find it better to use another way.
This SQL script contains 14 queries. Table "MyTable" is deleted, then recreated and filled with data. A transaction is started, data is inserted, then this last action is cancelled.
Another transaction is started. data is inserted, then changes are comitted. At last you can see what data is contained in the table after all queries.
Update.sql drop table MyTable;
create table MyTable (ID AutoInc, val1 Integer, val2 string(10));
insert into MyTable (val1, val2) values (2,'2');
insert into MyTable (val1, val2) values (4,'3');
insert into MyTable (val1, val2) values (5,'9');
insert into MyTable (val1, val2) values (7,'Hello');
update MyTable set val2='Many' where val1>2;
select * from MyTable;
This sample illustrates use of UPDATE statement.
This SQL script contains 8 queries. Table "MyTable" is deleted, then recreated and filled with data. The seventh query sets field "val2" to string 'Many' where "val1" is more than 2. At last you can see what data is contained in the table after all queries.